Fabrikam Office XP Web Service Connector Add-In Templates

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Charles Maxson
OfficeZealot.com

July 2002

Applies to:
     Microsoft® Office XP

Summary: The Fabrikam Office XP Web Services Integration (OWSI) Sample solution connects users with XML Web service data directly from within Microsoft Office applications. Fabrikam OWSI uses Office document templates (such as .xla and .dot) to enable ad hoc retrieval of data from XML Web services in Microsoft Excel and Microsoft Word. The Office templates, which build on top of the Fabrikam Office Web Services Integration Client Object Library, host the primary user interface, connection logic to the Fabrikam OWSI Client Object Library, and application- and feature-specific functionality to intelligently present the returned XML Web service data for natural use by the end user.

Usage Overview

The Fabrikam OWSI document templates make it very simple for users to get XML Web service data into Office documents. In fact, the users of the system need not even be aware that the applications are using XML Web services at all; they can just go about their tasks of working with documents as they already do. The templates offer a passive, non-intrusive user interface that works as if it were simply an extension of Microsoft Office. Figure 1 shows the modeless form which is based on a simple hierarchal TreeView control and can be hidden or displayed by the user using either the custom menu or toolbar commands that are created when the add-ins load.

Figure 1. Fabrikam Web Service Connector main user interface.

One of the design goals of the add-ins' user interface was to enable non-technical users to utilize XML Web services without requiring any knowledge or training, and to be able to leverage external data using terminology that they naturally understand – that is, the language of their business. This is accomplished by masking the XML Web service jargon such as service or method with business terms like customer order history, wholesale price, or supplier listing. Furthermore, these functions, or actions as they are referred to, are grouped into logical operating categories such as orders, products, employees, or customers.

Beside business terminology, actions can also be displayed as functional elements that are specific to the Office applications. For example, Excel has action categories that include Chart, PivotTable, and worksheet Function while Word has Table and Bulleted Lists actions. Users can toggle between business and functional groupings by invoking a context-sensitive menu that is displayed via a right-mouse click event over the form. Other features available from this menu include options to expand or collapse all tree nodes, manage the form's AutoSize feature, and to invoke the selected action.

To initiate an action to be carried out, users navigate to the desired action in the TreeView list (actions are recognizable as childless nodes set in bold font) and double-click or use the right-mouse context menu Insert command. Then they will be prompted for the properties that the requested action and underlying XML Web services require such as input parameters, whether to override the default formatting, and to set the desired output location of the resulting action.

The location options range from the current selection, as designated by the area highlighted by the cursor when the action was invoked, to using a new workbook in Excel or new document in Word. As you would expect, any data existing in the current selection will be replaced by the action output. Also of note is that the user interface serves as the launching point for custom templates that accompany the system and not just data driven actions.

Once the properties dialog has been submitted, the action runs its affiliated XML Web service to retrieve the data by calling the Fabrikam OWSI Client Object Library. For more information on the Fabrikam OWSI Client Object Library, see the accompanying document Technical Overview of the Fabrikam Office Web Services Integration Sample Solution.

Once the Fabrikam OWSI Client Object Library has returned the data which the add-in receives as a VBA array, it is positioned in the location that was selected by the user. Finally, before control is turned back over to the user, any additional formatting and functionality that is required based on the on the category of the action is performed. For example, a chart action will carry out common charting functionality such as the creating the chart and assigning the data range as well as setting the default formatting properties using standardized logic. Then additionally, each action can apply its own predefined features to perform tasks such as setting properties like the chart type, title, x and y data range settings or building out its own custom formatting.

Once the action is complete and residing in the user's document, they can then simply begin using it as they would with any other Office object. Beyond that, users can extend any document using multiple actions to build out their own templates or applications.

Architecture and Design

There is a unique Web Service Connector Add-In Template for both Microsoft Excel and Microsoft Word. Both add-ins are document based and, of course, specific to each application. There is an .xla for Excel and a .dot for Word. By having document add-ins, there are several features of the Office add-in that are automatically extended to the templates such as the ability to auto load with the applications, robust event handling, visibility in an application's add-in user interface, and the ability to utilize template file location paths. Using document templates for the Web Service Connector application also offers some very distinct benefits. These include:

• Ease of install - No client registration is required; simply copy the file to the application's template directory.
• Application independence - Allows users to load on demand or load only a specific application at a time. For instance, the user might only want to load the Excel version.
• Full functionality to the MS Forms 2.0 package – Since the UI was designed to be modeless (floats over the top of the Office applications without requiring immediate processing), the tighter integration between the document add-in architecture and MS Forms made this an automatic feature.
• Ease of development - Offering faster, simplified debugging and access to code.

Besides the Office add-in components including VBA, MS Forms and type libraries for Office and each specific application, the Web Service Connector Add-In Templates also make reference to Microsoft MSXML 4.0 (msxml4.dll) to parse the XML action metadata document, the Microsoft Windows Common Controls 6.0 (SP4) for usage of the TreeView and ImageList controls, as well as the custom Fabrikam Office Web Services Integration Client Object type library to handle all the XML Web service plumbing.

Technical Overview and Highlights

One of the main benefits of using the Fabrikam OSWI Client Object Library is that you get all the advantages associated with XML Web services as a data source without having to extend any client code within Office beyond traditional Visual Basic or VBA. So technically, the templates are actually not doing much more out of the ordinary, which is actually a highlight in itself. Allowing the Fabrikam OSWI Client Object Library to do all of the "heavy lifting" in respect to connecting to XML Web services and parsing the XML Web service response, the templates simply make calls to the Fabrikam OSWI Client Object Library and get back data in the form of strings and arrays which can be handled as they usually would be with VBA.

None of the XML Web services details or supporting data elements such as parameters are actually stored within the templates. All of this configuration data is loaded at run time from an XML document that holds this information. The templates then use the MSXML parser to use this data for populating the user interface's TreeView control and making the Web service calls. Listing 1 has an outline of the XML schema that the templates expect from an XML document name "Actions.xml".

<DataRoot>
    <ActionItem>
        <Include>
        <Service>
        <ServiceImage>
        <Group>
        <GroupImage>
        <Action>
        <ActionImage>
        <FunctionName>
        <OnAction>
        <WebService>
        <WebMethod>
        <Parameters>
            <Parameter>

Listing 1. XML outline used to define an Office action.

The templates require that the XML file resides in the same folder location as they do. Note that one XML file can be used by both the Word and Excel add-ins as long as the Include element in the XML file designate what application each action belongs to. The Include value representing a Word Action is wd and xl is used for Excel. The OWSI system leverages the Excel 2002 XML features to use an Excel file (ActionManager.xls) to create and manage the Action.xml XML document. But also noteworthy is that any source application that produces XML could easily apply the schema to create the document and publish it from a Web server to centralize the administration process.

As mentioned earlier, the main user interface for the templates is modeless. This offers better usability since users can continue to work while the user interface is easily accessible but visually at their disposal. To keep the user interface window from becoming a burden on screen resources while it is visible, it has a feature that responds to TreeView events to automatically resize it for efficient display. Users can also manually size the window as they like. Since the MS Forms 2.0 package does have native resizing capabilities, several Windows application programming interface (API) calls are made to make the form sizable and to trim the dialog object down for an even smaller screen footprint.

Each action runs through the exact same internal process for retrieving XML Web service data and positioning the output location. The different ways an action's output is ultimately handled is a function of the type of action being called; for example, a PivotTable report has its own unique process for presenting the data compared to a chart, compared to a list, and so on. All the actions of a similar type are run through the same routines that perform preparation and basic formatting. It is not until the very end of the action process that specific (static) business logic is applied; for example, what are a PivotTable report's row, column, and page fields, how is it formatted, and so on. The following graphic in Figure 2 depicts the action process for the OWSI Web Service Connector add-In templates:

Figure 2. Process flow diagram for the Fabrikam Web Service Connector Templates (click thumbnail for larger image).

Technical Roadmap

The following table is a high level roadmap for the Fabrikam Web Service Connector Template Add-ins. For lower level detail, please refer to the code.